package com.l.dbtransfer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.l.dbtransfer.utils.DbUtils;

/**
 * @author 严秋旺
 * @date 2017-11-17 16:23:35
 */
public class Transfer {

    public void start(String table) throws ClassNotFoundException, SQLException {
        Connection srcConn = DbUtils.createConnection("src");
        Connection dstConn = DbUtils.createConnection("dst");

        Statement statement = srcConn.createStatement();
        long total = count(statement, table);
        ResultSet rs = statement.executeQuery("select * from " + table);
        PreparedStatement ps = createCopyPrepare(table, rs, dstConn);

        copyData(total, rs, ps);

        rs.close();
        ps.close();
        srcConn.close();
        dstConn.close();
    }

    private long count(Statement statement, String table) throws SQLException {
        ResultSet rs = statement.executeQuery("select count(1) from " + table);
        long count = 0;

        if (rs.next())
            count = rs.getLong(1);

        rs.close();
        return count;
    }

    private void copyData(long total, ResultSet rs, PreparedStatement ps) throws SQLException {
        int columnCount = rs.getMetaData().getColumnCount();
        long count = 0;

        while (rs.next()) {
            count++;
            try {
                for (int i = 1; i <= columnCount; i++) {
                    Object val = rs.getObject(i);
                    ps.setObject(i, val);
                }

                ps.executeUpdate();
                System.out.print("\r数据复制：" + total + "\\" + count);
            } catch (Exception e) {
                System.out.println("数据复制失败：" + e.getMessage());
            }
        }
    }

    private PreparedStatement createCopyPrepare(String table, ResultSet rs, Connection conn) throws SQLException {
        int columnCount = rs.getMetaData().getColumnCount();
        StringBuilder sql = new StringBuilder("insert into ");
        sql.append(table).append(" values (");

        for (int i = 0; i < columnCount; i++) {
            if (i > 0)
                sql.append(',');
            sql.append('?');
        }

        sql.append(")");
        return conn.prepareStatement(sql.toString());
    }

}
